﻿-- 获取没有面部数据的人员
CREATE procedure [dbo].[RyInfo_GetHaveNoFace]
@sfzh varchar(18) = null,
@xm varchar(10) = null,
@bzid varchar(20) = null
as
select Ry_Info.sfzh,Ry_Info.xm,isnull(bb.bzmc,'')+'\'+aa.bzmc bzmc,fbmc
from Ry_Info
left join RyFace_Info on(Ry_Info.sfzh=RyFace_Info.sfzh)
left join Fenbry_Info on(Ry_Info.sfzh=Fenbry_Info.sfzh)
left join Fenb_Info on(Fenbry_Info.fbid=Fenb_Info.fbid)
left join Bzry_Info on(Ry_Info.sfzh=Bzry_Info.sfzh)
left join Banz_Info aa on(Bzry_Info.bzid=aa.bzid)
left join Banz_Info bb on(bb.bzid=aa.bzfid)
where RyFace_Info.sfzh is null
and (@sfzh is null or Ry_Info.sfzh like @sfzh+'%')
and (@xm is null or Ry_Info.xm like '%'+@xm+'%')
and (@bzid is null or aa.bzid like @bzid+'%')